--exec sp_substitutable_part_numbers_program_release 471, 1130, 0

ALTER PROC sp_substitutable_part_numbers_program_release
@releaseId	bigint,
@processStepId	bigint,
@loadMappedParts bit
AS
BEGIN

	DECLARE @lot_id					INT
	DECLARE @substitute_product_processstep_id	INT
	DECLARE @part_number				VARCHAR(50)
	DECLARE @substitutable_part_number_table TABLE   
				([Product_ProcessStep_Id]			INT,
				 [Substitute_Product_ProcessStep_Id]		INT,
			 	 [Part_Number]					VARCHAR(50),
				 [Is_Selected]					BIT)
	if( @loadMappedParts != 0)
	begin
		INSERT INTO @substitutable_part_number_table 
			SELECT  PST.Product_ProcessStep_Id, PST.Substitute_Product_ProcessStep_Id, Product.Part_Number, 1
	                            FROM Product_Substitutables_Table PST, Product where PST.Substitute_Product_ProcessStep_Id = Product.ProcessStep_ID
	                            and PST.Product_ProcessStep_Id = @processStepId
	end
	else
	BEGIN
		INSERT INTO @substitutable_part_number_table 
			SELECT  PST.Product_ProcessStep_Id, PST.Substitute_Product_ProcessStep_Id, Product.Part_Number, 1
	                            FROM Product_Substitutables_Table PST, Product where PST.Substitute_Product_ProcessStep_Id = Product.ProcessStep_ID
	                            and pst.Substitute_Product_ProcessStep_Id not in ( select processstep_id from Prog_Release_Substitued_Part_Numbers where prog_release_id = @releaseId )
	                            and PST.Product_ProcessStep_Id = @processStepId

		insert into @substitutable_part_number_table 
			 SELECT  @processStepId, prspn.processStep_Id, p.Part_Number, 0
                                FROM Prog_Release_Substitued_Part_Numbers prspn, Product p, program_release pr  where prspn.processStep_Id = P.ProcessStep_ID
                                and prspn.prog_release_id = pr.prog_release_id and pr.prog_release_id = @releaseId and pr.blank_processstep_id = @processStepId
	END
	SELECT Product_ProcessStep_Id, Substitute_Product_ProcessStep_Id, Part_Number, Is_Selected  FROM @substitutable_part_number_table order by part_number
END


